--EXEC SP_CREATE_UPDATE_RECEIVE_TRANSACTION_LOT 'A8002-3','B020B','04/06/2010 18:32:27', 11038, 'UTAC', null,null, 'Assembly'

ALTER PROCEDURE SP_CREATE_UPDATE_RECEIVE_TRANSACTION_LOT
(
 @PART_NUMBER		VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50),
 @DATE_IN 		DATETIME,
 @QTY_IN 		INT,
 @VENDOR_NAME 		VARCHAR(50),
 @Fab_Lot_No		varchar(50),
 @DATE_CODE			VARCHAR(50),
 @PS_TYPE			VARCHAR(50) 
 )
 AS
 BEGIN
	
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @EXIST_DATE_CODE VARCHAR(50)
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	
	Select @PS_TYPE_NAME = BASE_PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @PS_TYPE
	
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
 	SELECT @PROCESS_STEP_ID = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@PART_NUMBER,@PS_TYPE_NAME,@PS_TYPE )
	IF @PROCESS_STEP_ID IS NULL 
	BEGIN
		 -- DUMMY UPDATE TO AVOID SQLEXCEPTION
		UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
		RETURN
	END
	
 	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	
 	IF @LOT_ID IS NULL -- Check if the lot exists
 	BEGIN 	
		-- Create Lot
 		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, LOT_DATE_CODE) 
				VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @Fab_Lot_No, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, 'Close', CURRENT_TIMESTAMP, 1 , 'PROD', 0 , @DATE_CODE)
		SELECT @LOT_ID = @@IDENTITY

		INSERT INTO LOT_TABLE_HISTORY ( LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, LOT_DATE_CODE,LOT_COMMENTS) 
				VALUES (@LOT_ID, @PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @Fab_Lot_No, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, 'Close', CURRENT_TIMESTAMP,1, 'PROD', 0 , @DATE_CODE, 'Transaction sheet In qty')		
		--INSERT INTO Lot_Receive_Table ( LOT_ID, RECEIVE_QTY, RECEIVE_DATE, TIMESTAMP) 
		--		VALUES (@LOT_ID, @QTY_IN, @DATE_IN, CURRENT_TIMESTAMP )		

		IF @PS_TYPE_NAME = 'Die Bank' OR @PS_TYPE_NAME = 'Class Store' OR @PS_TYPE_NAME = 'Finished Goods'
		BEGIN
			insert into Inventory_Lot( Lot_ID, Timestamp ) values (@LOT_ID, CURRENT_TIMESTAMP)		
		END
		ELSE IF @PS_TYPE_NAME = 'Assembly' 
		BEGIN
			insert into Assembly_Lot( Lot_ID, Timestamp ) values (@LOT_ID, CURRENT_TIMESTAMP)		
		END
		ELSE IF @PS_TYPE_NAME = 'TEST' 
		BEGIN
			insert into Final_Test_Lot( Lot_ID, Timestamp ) values (@LOT_ID, CURRENT_TIMESTAMP)		
		END
	END
	ELSE
	BEGIN
		
		IF (SELECT COUNT(*) FROM LOT_TABLE_HISTORY WHERE LOT_ID = @LOT_ID AND LOT_ENTERED_BY = 1 AND  LOT_CURRENT_STATUS = 'Close' ) = 0
		BEGIn
			INSERT INTO LOT_TABLE_HISTORY ( LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, LOT_COMMENTS) 
				VALUES (@LOT_ID, @PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @Fab_Lot_No, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, 'Close', CURRENT_TIMESTAMP,1, 'PROD', 0 , 'Transaction sheet In qty')		
		END

		SELECT @EXIST_DATE_CODE = LOT_DATE_CODE FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
		IF @EXIST_DATE_CODE IS NULL AND @DATE_CODE IS NOT NULL
		BEGIN
				UPDATE LOT_TABLE SET LOT_DATE_CODE = @DATE_CODE WHERE LOT_ID = @LOT_ID
		END
		/*IF (SELECT COUNT(*) FROM Lot_Receive_Table WHERE LOT_ID = @LOT_ID AND RECEIVE_DATE = @DATE_IN AND  RECEIVE_QTY = @QTY_IN ) = 0
		BEGIn
			INSERT INTO Lot_Receive_Table ( LOT_ID, RECEIVE_QTY, RECEIVE_DATE, TIMESTAMP) 
				VALUES (@LOT_ID, @QTY_IN, @DATE_IN, CURRENT_TIMESTAMP )
		END */
	END

	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	--EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @FAB_LOT_NO, @QTY_IN, @DATE_IN

END